package sunlight.ui.models;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;

import javax.swing.table.DefaultTableModel;

import org.apache.commons.lang.StringEscapeUtils;

import sunlight.core.Application;
import sunlight.database.DBConnectionLostException;
import sunlight.database.DatasourceHelper;
import sunlight.database.MySqlDatabase;
import sunlight.logger.EventLogger;

public class TableFromDatabaseModel extends DefaultTableModel {

	/**
	 * 
	 */
	private static final long serialVersionUID = 3418616782284075667L;
	
	private String[] 	columnNames;
	private boolean[] 	columnEditable;
	
	protected String 	SQLQuery;
	protected String[]	dbFields;
	protected String 	dbTable;
	
	protected DatasourceHelper 	DbInfo;
	protected MySqlDatabase 	Database;
	
	public TableFromDatabaseModel(){
		this.aquireDatabase();
	}
	
	public TableFromDatabaseModel(String table, String[] fields, String[] columnNames, boolean[] editableColumns){
		this.dbTable		= table;
		this.dbFields		= fields;
		this.columnNames 	= columnNames;
		this.columnEditable = editableColumns;
		
		this.setColumnIdentifiers(this.columnNames);
		
		this.aquireDatabase();
	}
	
	public TableFromDatabaseModel(String[] columnNames){
		this.columnNames = columnNames;
		
		for(int i=0; i<this.columnNames.length; i++){
			this.columnEditable[i] = true;
		}
		
		this.setColumnIdentifiers(this.columnNames);
		
		this.aquireDatabase();
	}
	
	public TableFromDatabaseModel(String[] columnNames, boolean[] editableColumns){
		this.columnNames 	= columnNames;
		this.columnEditable = editableColumns;
		
		this.setColumnIdentifiers(this.columnNames);
		
		this.aquireDatabase();
	}
	
	@Override
	public int getColumnCount() {
		return columnNames.length;
	}
	
	@Override
	public boolean isCellEditable(int rowIndex, int columnIndex) {
		return columnEditable[columnIndex];
	}
	
	public void setColumnNames(String[] columnNames){
		this.columnNames = columnNames;
		
		this.setColumnIdentifiers(this.columnNames);
	}
	
	public void aquireDatabase(){
		this.DbInfo = new DatasourceHelper();
		this.Database = DbInfo.getDbObject();
	}
	
	public void setDatabase(MySqlDatabase newDatabase){
		this.Database = newDatabase;
	}
	
	public void setDatabaseFieldsToSelect(String[] fields){
		this.dbFields = fields;
	}
	
	public void setSQLQuery(String query){
		this.SQLQuery = query;
	}
	
	public void setDatabaseTable(String table){
		this.dbTable = table;
	}
	
	public void fillTableDataFromDatabase(String sqlQuery, String table, String[] fields, boolean escape){
		
		this.setSQLQuery(sqlQuery);
		this.setDatabaseFieldsToSelect(fields);
		this.setDatabaseTable(table);
		
		ResultSet rows = (ResultSet) this.Database.executeQuery(this.SQLQuery, escape);
		
		try {
			if(rows!=null){
				while(rows.next()){
					Object[] rowData = new Object[this.dbFields.length];
					
					for(int i=0; i<this.dbFields.length; i++){
						rowData[i] = rows.getString(this.dbFields[i]);
					}
					
					this.addRow(rowData);
				}
			}
		} catch (SQLException e) {
			Application.Log.write(e.toString(), EventLogger.ERROR);
		}
	}
	
	public void fillTableDataFromDatabase(String sqlQuery, String table){
		this.fillTableDataFromDatabase(sqlQuery, table, this.dbFields, true);
	}
	
	public void fillTableDataFromDatabase(String sqlQuery, boolean escape){
		this.fillTableDataFromDatabase(sqlQuery, this.dbTable, this.dbFields, escape);
	}
	
	public void fillTableDataFromDatabase(String sqlQuery){
		this.fillTableDataFromDatabase(sqlQuery, this.dbTable, this.dbFields, true);
	}
	
	public void fillTableDataFromDatabase(){
		this.fillTableDataFromDatabase(this.SQLQuery, this.dbTable, this.dbFields, true);
	}
	
	public void refresh(){
		this.emptyTable();
		this.fillTableDataFromDatabase();
	}
	
	
	//TODO add comments...
	public void filter(String[] SQLOperators, String SQLSelectOparator, HashMap<?, ?> fieldsAndValues){
		this.emptyTable();	
		
		String query = "SELECT * FROM " + StringEscapeUtils.escapeSql(this.dbTable) + " ";
		
		query += this.Database.makeSQLFilterQuery(SQLOperators, SQLSelectOparator, fieldsAndValues);

		this.fillTableDataFromDatabase(query, false);
	}
	
	//TODO add comments...
	public void emptyTable(){
		int rows_count = this.getRowCount()-1;
		
		for(int i=rows_count; i>=0; i--){
			 this.removeRow(i);
		 }
	}
	
	//TODO add comments...
	public void deleteRowFromDatabase(int rowIndex, int colIndex){
		if((this.dbFields.length > 0) && (this.dbTable.length() > 0)){
			
			String identifier = (String)this.getValueAt(rowIndex, colIndex);
			String dbColName  = this.dbFields[colIndex];
			
			HashMap<String, String> where = new HashMap<String, String>();
			
			where.put(dbColName, identifier);
			
			try {
				boolean result = this.Database.delete(this.dbTable, where, null, null);
				
				if(result){
					this.removeRow(rowIndex);
				}
				
			} catch (DBConnectionLostException e) {
				Application.Log.write(e.toString(), EventLogger.ERROR);
			}
		}
	}
}
